
*Publicly available CBSA crosswalk (Feb 2013 version: https://www.census.gov/geographies/reference-files/time-series/demo/metro-micro/delineation-files.html) 
*merged with ZIP code lat/long data
use "$input\Geo Crosswalk Master.dta", clear
keep  zip fips5 cbsa cbsa_title msa_type state_code intptlat intptlong
ren (zip fips5 cbsa cbsa_title msa_type state_code intptlat intptlong) (pt_zip pt_fips pt_cbsa pt_cbsa_title pt_msa_type pt_state pt_lat pt_long)
save "$temp\geo info.dta", replace


foreach yr in 11 12 13 14 {
	
	*CMS weights avialable from IPPS Final Rules: https://www.cms.gov/Medicare/Medicare-Fee-for-Service-Payment/AcuteInpatientPPS/IPPS-Regulations-and-Notices
	insheet using "$input\FY 20`yr' DRG WTS.csv", comma clear
	keep msdrg type weights	
	ren (msdrg type weights) (drg drg_type`yr' drg_wt`yr')
	xtile drg_wt_qnt = drg_wt`yr', nq(5)
	save "$temp\CMS DRG wts`yr'.dta", replace
	
	*Load insurer HHIs from AMA Insurer Competition Reports
	import excel "$input\MSA insurer HHIs.xls", sheet("20`yr'") firstrow clear
	ren FIPSStateCode hosp_state
	ren CBSACode hosp_cbsa
	ren PPO cbsa_PPO_hhi
	ren POS cbsa_POS_hhi
	drop CBSATitle
	gen yr = "20`yr'"
	save "$temp\cbsa ins hhi`yr'.dta", replace
	
	import excel "$input\Insurer state shares.xlsx", sheet("20`yr'") firstrow clear
	drop state_name
	gen str2 hosp_state = string(statefips, "%02.0f")
	drop statefips
	tostring hosp_state, replace
	ren (ppo_hhi pos_hhi) (PPO_hhi POS_hhi)
	gen yr = "20`yr'"		
	save "$temp\state ins hhi`yr'.dta", replace

	*State and county income and unins available at: http://www.census.gov/did/www/sahie/ and http://www.census.gov/did/www/saipe/index.html
	import excel "$input\cnty_stats.xlsx", sheet("20`yr'") firstrow clear
	
	preserve
		drop if geocat == 50
		tostring year, gen(yr)
		gen str2 hosp_state = string(statefips, "%02.0f") 
		drop geocat statefips countyfips year
		save "$temp\state inc and unins`yr'.dta", replace 
	restore
	
	drop if geocat == 40
	tostring year, gen(yr)
	gen str2 hosp_state = string(statefips, "%02.0f")
	gen str3 county_txt = string(countyfips, "%03.0f")
	gen hosp_fips = hosp_state + county_txt
	drop geocat statefips county_txt countyfips year
	save "$temp\county inc and unins`yr'.dta", replace 
}


foreach yr in 11 12 13 14 {
	
*Load claims
use "$temp\claims_clean`yr'.dta", clear
gen str5 pt_zip = string(mbr_zip_5_cd, "%05.0f")
merge m:1 pt_zip using "$temp\geo info.dta"
drop if _m == 2
drop _m

*merge with AHA
di "merge AHA"
merge m:1 aha_hnpi using "$output\aha`yr'.dta" 
keep if _m == 3
drop _m
gen hosp_state_abbrv = hosp_state 
replace hosp_state = substr(hosp_fips, 1, 2)

*merge CMS wts
di "merge DRG wts"
merge m:1 drg using "$temp\CMS DRG wts`yr'.dta"
keep if _m == 3
drop _m
	
*merge insurer market share at hospital level
di "merge ins hhi"
merge m:1 yr hosp_state hosp_cbsa using "$temp\cbsa ins hhi`yr'.dta", keepus(cbsa_PPO_hhi cbsa_POS_hhi) 
drop if _m == 2
drop _m

merge m:1 yr hosp_state using "$temp\state ins hhi`yr'.dta", keepus(PPO_hhi POS_hhi) 
drop if _m == 2
drop _m

replace cbsa_PPO_hhi = PPO_hhi if cbsa_PPO_hhi == .
replace cbsa_POS_hhi = POS_hhi if cbsa_POS_hhi == .

*merge income & unins
di "merge income & unins rates"
merge m:1 hosp_fips using "$temp\county inc and unins`yr'.dta"
drop if _m == 2
drop _m

save "$temp\analysis_claims`yr'.dta", replace

}

exit
